package com.cnooc.zyfx;


import com.cnooc.zyfx.entity.Entities;
import com.cnooc.zyfx.entity.Entities.queryKPI;
import com.github.thorqin.toolkit.Application;
import com.github.thorqin.toolkit.annotation.Service;
import com.github.thorqin.toolkit.db.DBService;
import com.github.thorqin.toolkit.db.DBService.DBTable;
import com.github.thorqin.toolkit.validation.annotation.Validate;
import com.github.thorqin.toolkit.web.HttpException;
import com.github.thorqin.toolkit.web.annotation.*;
import com.github.thorqin.toolkit.web.router.WebContent;
import com.github.thorqin.toolkit.web.session.WebSession;
import com.github.thorqin.toolkit.utility.Serializer;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.joda.time.DateTime;

@WebModule(value="/api")
public class CommentsModule {

   // @Service("db")
   // DBService db;
	
	@Service("application")
    Application app;
	
    @Service("sqlServer")
    DBService msdb;
    
    public static class Obj {
		public String key;
		public String value;
		
	}
    
    @WebEntry(method = HttpMethod.GET)
    public WebContent getSectionList1() throws SQLException, InstantiationException, IllegalAccessException {
        try (DBService.DBSession session = msdb.getSession(false)) {
            List<Obj> db= session.queryList("select BuzBoardCode 'key', BuzBoardName value from MDBuzBoard", Obj.class);
            return WebContent.jsonString(Serializer.toJsonString(db));
        }
    }
    
    @WebEntry(method = HttpMethod.GET)
    public DBTable getSectionList() throws SQLException, InstantiationException, IllegalAccessException {
        try (DBService.DBSession session = msdb.getSession(false)) {
            return session.queryTable("select BuzBoardCode 'key', BuzBoardName value from MDBuzBoard");
        }
    }
    
    @WebEntry(method = HttpMethod.POST)
    public  DBTable getKPI(@Entity queryKPI input) throws SQLException {
        try (DBService.DBSession session = msdb.getSession(false)) {
            return session.queryTable("select FieldCode 'key', FiledName value, 'false' checked from MDPrdKPI where BuzBoardCode=? or ? is null",input.sectionId,input.sectionId);
        }
    } 
    
    @WebEntry(method = HttpMethod.POST)
    public  DBTable getCompany(@Entity queryKPI input) throws SQLException {
        try (DBService.DBSession session = msdb.getSession(false)) {
            return session.queryTable("select CompCode 'key', DeptName value, 'false' checked From MDPrdComp where BuzBoardCode=? or ? is null",input.sectionId,input.sectionId);
        }
    } 
    
//    @WebEntry(method = HttpMethod.POST)
//    public  DBTable getSectionByKPI(@Entity Entities.querySection input) throws SQLException {
//        try (DBService.DBSession session = msdb.getSession(false)) {
//            return session.queryTable("select BuzBoardCode 'key', BuzBoardName value from MDBuzBoard where id = "
//            		+ "(select BuzBoardCode from  MDPrdKPI  where FieldCode = ?)",input.KPI);
//        }
//    } 
    
    private String makeSql(Entities.queryComentInput input, List<Object> args) {
    	String sql = "select #FIELD# from ProExpertComments e join ( " +
        		" select  b.BuzBoardCode,b.BuzBoardName, p.FiledName, p.FieldCode " +
        		" from MDBuzBoard b join MDPrdKPI p on b.BuzBoardCode=p.BuzBoardCode " +
        		" where 1=1 ";
    	if (input.sectionId != null) {
    		sql += " and b.BuzBoardCode=?";
    		args.add(input.sectionId);
    	}else{
    		sql += " and b.BuzBoardCode in (?)";
    		args.add(input.sections);
    	}
    	if (input.KPIs != null && input.KPIs.size() > 0) {
    		sql += " and p.FieldCode in (?)";
    		args.add(input.KPIs);
    	}
    	sql += ") t on e.FieldCode=t.FieldCode";
    	return sql;
    }
    
    /*comments functions*/
    @WebEntry(method = HttpMethod.POST)
    public DBTable queryExpertComments(@Entity @Validate Entities.queryComentInput input) throws SQLException, IOException {
        try (DBService.DBSession session = msdb.getSession(false)) {
        	String pageBegin = "select top 10 * from (";
        	String pageEnd = ") t where num > ?";
        	String fieldList = "0 checked,e.ID,ROW_NUMBER() over(order by ReportDate desc) as num,"
    			+ "t.FieldCode,t.FiledName,Comments,BuzBoardCode,BuzBoardName,user_id,"
    			+ "case when UpdateUser is null then CreateUser else UpdateUser end lastEditUser,"
        		+ "CreateTime,UpdateTime,ReportDate,ReportYear";
        	List<Object> args = new LinkedList<>();
        	String sql = makeSql(input, args);
        	String querySql = pageBegin + sql.replace("#FIELD#", fieldList) + pageEnd;
        	String countSql = sql.replace("#FIELD#", "count(0) as ct");
        	
        	int len = (Integer)session.queryFirst(countSql, args.toArray()).get("ct");
        	args.add(10 * (input.page-1));
            DBTable tb = session.queryTable(querySql,args.toArray());
            tb.length = len;
            return tb;
        }
    }
    
    @WebEntry(method = HttpMethod.POST)
    public void addExpertComments(@Entity @Validate Entities.addCommentInput input,WebSession websession) throws SQLException, IOException {
	    try (DBService.DBSession session = msdb.getSession()) {
	    	String userName=(String)websession.get("name");
	    	session.execute(app.readScript("sql/addComment.sql"), new Date(), new DateTime().getYear(), input.KPI,input.comments,
	    			userName,new Date(),(String)websession.get("uid"),new Date(),userName);
	    } catch(SQLException e){
	    	if (e.getMessage().startsWith("Cannot insert duplicate key")) {
	    		throw new HttpException(400,"该条专家意见已存在");
	    	}
	    	if (e.getMessage().startsWith("String or binary data would be truncated")) {
	    		throw new HttpException(400,"字数超过限制");
	    	}
	    }
	}
    
    @WebEntry(method = HttpMethod.POST)
    public void deleteExpertComments(@Entity Entities.deleteInput input) throws SQLException {
        try (DBService.DBSession session = msdb.getSession()) {
            session.execute("delete from ProExpertComments where id in (?)",input.id);
        }
    }
    
    @WebEntry(method = HttpMethod.POST)
    public void modifyExpertComments(@Entity @Validate Entities.modifyCommentInput input,WebSession websession) throws SQLException, IOException {
	    try (DBService.DBSession session = msdb.getSession()) {
	    	session.execute(app.readScript("sql/modifyComment.sql"),input.comments,
	    			(String)websession.get("name"),new Date(),(String)websession.get("uid"),input.commentId );
	    }catch (SQLException e) {
			if (e.getMessage().startsWith("String or binary data would be truncated")) {
	    		throw new HttpException(400,"字数超过限制");
	    	}
		}
	}
    
    
}

